# -*-coding:utf-8-*-
import sqlite3

from common import msaccess
from common import config

__author__ = 'Jason'

def insert(dic):
    cx = sqlite3.connect(config.getMainDb())

    cu = cx.cursor()
    for key in dic:
        try:
            cu.execute("select code from BookClass where code='{0}'".format(key.strip().replace("[","").replace("]","")))
            one = cu.fetchone()
            if not one:
                cu.execute("insert into BookClass(RawCode,Code,Title) values('{0}','{1}','{2}')".format(key.strip(),key.strip().replace("[","").replace("]",""), dic[key].strip()))

        except Exception as e:
            print(e)
            print(key)
    cx.commit()

    cx.close()
    return

def loadBookCategroy(file):
    cx = sqlite3.connect(file)

    cu = cx.cursor()
    cu.execute("select Code,Title from BookClass")
    cates = cu.fetchall()
    cx.close()

    cateDic = {} #字典
    for cate in cates:
        cateDic[cate[0]] = cate[1]

    return cateDic

def clearBookCategroy(file):
    cx = sqlite3.connect(file)

    cu = cx.cursor()
    cu.execute("select * from BookClass")
    cates = cu.fetchall()

    for item in cates:
        cu.execute("update BookClass set RawCode='{0}' where Title='{1}'".format(item[0],item[2]))
    cx.commit()
    cx.close()
    return cates


def migrate(mdb_file,db_file):
    source = msaccess.load(mdb_file)

    cx = sqlite3.connect(db_file)

    cu = cx.cursor()
    for item in source:
        # cu.execute("update BookClass set BookClassC='{0}' where BookClassE='{1}'".format(item[1],item[0]))
        try:
            cu.execute("select code from BookClass where code='{0}'".format(item[0].strip().replace("[","").replace("]","")))
            one = cu.fetchone()
            if not one:
                cu.execute("insert into BookClass(RawCode,Code,Title) values('{0}','{1}','{2}')".format(item[0].strip(),item[0].strip().replace("[","").replace("]",""), item[1].strip()))
        except Exception as e:
            print(e)
    cx.commit()

    cx.close()
    return